﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace CNotebook.Sales
{
    public class KitManager:ConnectionManager
    {
        #region Adding/Updating
        public void AddBOM(KitUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "INSERT INTO OKIT (BOMCode, BOMName, ItemCode, ItemName, Qty, UoM, IsProduction, IsSales) VALUES (@BOMCode, @BOMName, @ItemCode, @ItemName, @Qty, @UoM, @IsProduction, @IsSales)";
            
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
            oCommand.Parameters.Add(new SqlParameter("@BOMName", oUnit.BOMName));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@Qty", oUnit.Qty));
            oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));
            oCommand.Parameters.Add(new SqlParameter("@IsProduction", oUnit.IsProduction));
            oCommand.Parameters.Add(new SqlParameter("@IsSales", oUnit.IsSales));
            

            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                oCommand = new SqlCommand();
                oCommand.Connection = this.Connection;
                oCommand.CommandText = "INSERT INTO KIT1 (BOMCode, ItemGroup, ItemCode, ItemName, Qty, UoM, IsFree) VALUES (@BOMCode, @ItemGroup, @ItemCode, @ItemName, @Qty, @UoM, @IsFree)";
                oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                oCommand.Parameters.Add(new SqlParameter("@ItemGroup", oRow["ItemGroup"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                oCommand.Parameters.Add(new SqlParameter("@IsFree", oRow["IsFree"]));

                oCommand.ExecuteNonQuery();
            }
        }

        public void UpdateBOM(KitUnit oUnit)
        {
            SqlCommand oCommand = new SqlCommand();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "UPDATE OKIT SET BOMName=@BOMName, ItemCode=@ItemCode, ItemName=@ItemName, Qty=@Qty, UoM=@UoM, IsProduction=@IsProduction, IsSales=@IsSales WHERE bomcode=@BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
            oCommand.Parameters.Add(new SqlParameter("@BOMName", oUnit.BOMName));
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", oUnit.ItemCode));
            oCommand.Parameters.Add(new SqlParameter("@ItemName", oUnit.ItemName));
            oCommand.Parameters.Add(new SqlParameter("@Qty", oUnit.Qty));
            oCommand.Parameters.Add(new SqlParameter("@UoM", oUnit.UoM));
            oCommand.Parameters.Add(new SqlParameter("@IsProduction", oUnit.IsProduction));
            oCommand.Parameters.Add(new SqlParameter("@IsSales", oUnit.IsSales));
            

            oCommand.ExecuteNonQuery();

            oCommand = new SqlCommand();
            oCommand.Connection = this.Connection;
            oCommand.CommandText = "DELETE KIT1 WHERE BOMCODE = @BOMCODE";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
            oCommand.ExecuteNonQuery();

            foreach (DataRow oRow in oUnit.LineItems.Rows)
            {
                if (oRow.RowState != DataRowState.Deleted)
                {
                    oCommand = new SqlCommand();
                    oCommand.Connection = this.Connection;
                    oCommand.CommandText = "INSERT INTO KIT1 (BOMCode, ItemGroup, ItemCode, ItemName, Qty, UoM , IsFree) VALUES (@BOMCode, @ItemGroup, @ItemCode, @ItemName, @Qty, @UoM, @IsFree )";
                    oCommand.Parameters.Add(new SqlParameter("@BOMCode", oUnit.BOMCode));
                    oCommand.Parameters.Add(new SqlParameter("@ItemGroup", oRow["ItemGroup"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemCode", oRow["ItemCode"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@ItemName", oRow["ItemName"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@Qty", oRow["Qty"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@UoM", oRow["UoM"].ToString()));
                    oCommand.Parameters.Add(new SqlParameter("@IsFree", oRow["IsFree"]));

                    oCommand.ExecuteNonQuery();
                }
            }
        }
        #endregion

        #region Getting Datas

        public DataTable BrowseBOMByName(string sBOMName)
        {
            DataSet ds = new DataSet();
            sBOMName = '%' + sBOMName + '%';
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OKIT WHERE BOMName LIKE @BOMName";
            oCommand.Parameters.Add(new SqlParameter("@BOMName", sBOMName));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }


        public DataTable GetBOMByItemCode(string sItemCode)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OKIT WHERE ItemCode=@ItemCode";
            oCommand.Parameters.Add(new SqlParameter("@ItemCode", sItemCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }


        public DataTable BrowseBOMByCode(string sBOMCode)
        {
            DataSet ds = new DataSet();
            sBOMCode = '%' + sBOMCode + '%';
            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OKIT WHERE BOMCode LIKE @BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", sBOMCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetBOMByCode(string sBOMCode)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM OKIT WHERE BOMCode=@BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", sBOMCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];

        }

        public DataTable GetLineItems(string sBOMCode)
        {
            DataSet ds = new DataSet();

            SqlCommand oCommand = new SqlCommand();
            SqlDataAdapter oAdapter = new SqlDataAdapter();

            oCommand.Connection = this.Connection;
            oCommand.CommandText = "SELECT * FROM KIT1 WHERE BOMCode=@BOMCode";
            oCommand.Parameters.Add(new SqlParameter("@BOMCode", sBOMCode));

            oAdapter.SelectCommand = oCommand;
            oAdapter.Fill(ds);

            return ds.Tables[0];
        }

        public DataTable GetLineItems(string sBOMCode, int iQtyRequirement)
        {
            try
            {
                DataSet ds = new DataSet();

                SqlCommand oCommand = new SqlCommand();
                SqlDataAdapter oAdapter = new SqlDataAdapter();

                oCommand.Connection = this.Connection;
                oCommand.CommandText = "SELECT *, Qty * @QtyRequirement as TotalRqmt FROM KIT1 WHERE BOMCode=@BOMCode";
                oCommand.Parameters.Add(new SqlParameter("@BOMCode", sBOMCode));
                oCommand.Parameters.Add(new SqlParameter("@QtyRequirement", iQtyRequirement));

                oAdapter.SelectCommand = oCommand;
                oAdapter.Fill(ds);

                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }


        #endregion
    }
}
